clear all
set more off, perm
local mydir "`1'"
global Tables	`mydir'/XS/Tables
global Work	`mydir'/XS/DataWork
global Raw	`mydir'/XS/DataRaw
global WRDS	`mydir'/WRDS
/* ********************************************************************* */
* [1] LOAD QUARTERLY COMPUSTAT
gzuse using $WRDS/Compustat/crsp_a_ccm_q.dta.gz, clear  
rename *, lower
rename lpermco permco
rename lpermno permno
* deal with crsp-compustat code for missing values
ds, not(type string)  
foreach var in `r(varlist)' {
    replace `var' = . if `var' == 0.0001 | `var' == 0.0002 | `var' == 0.0003 | `var' == 0.0004 | `var' == 0.0008 
}
gen shout_compust=cshprq 
generate div=dvpsxq * cshoq

* Only data in USD
keep if index(curcdq, "USD")>0

generate steq=seqq					/* Stockholders Equity */
replace  steq=ceqq+pstkq		if steq==.	/* Common/Ordinary Equity + Preferred  */
replace  steq=atq-ltq			if steq==.	/* assets minus liabilities */

* compute book equity per share
* pstkrv: redemption value 
* pstkl:  liquidating value 
* pstk:   carrying value 
generate pfd=0 
replace  pfd=pstkq if pstkq<.

* txditc: taxes and investment tax credit
egen txdbitcb=rowtotal(txdbq itccy)	/* deferred taxes + investment tax credit; "rowtotal" treats missing as 0 */
egen dtax=rowfirst(txditcq txdbitcb)

* Ken writes on his website: "Because of changes in the treatment of deferred taxes described in FASB 109, files produced after August 2016 no longer add Deferred Taxes and Investment 
* Tax Credit to BE for fiscal years ending in 1993 or later."
replace dtax=0 if year(datadate)>=1993

generate bookeqLTM=(steq+dtax-pfd)
* clean surplus accouting
sort gvkey datadate
by gvkey: replace  bookeqLTM=bookeqLTM[_n-1]+ibq-div if bookeqLTM==.
label var bookeqLTM "Book value of equity (annual)"
* ********************
* compute street earnings and operating income
generate street=niq - xidoq - spiq - nrtxtq
replace  street=ibq		if street==.
replace  street=niq  		if street==.

generate oper=saleq-cogsq
replace  oper=oper-xsgaq if xsgaq<. 
replace  oper=oper-xintq if xintq<. 

gen fq=quarterly(datafqtr,"YQ")
format fq %tq
keep  linkprim linktype conm* permco permno datadate datacqtr fq fyr fyrc fyearq fqtr ibq niq street oper bookeqLTM div saleq shout_compust ppegtq ppentq atq capxy dpq dpactq intanq xrdq xsgaq sic adjex cshprq
order linkprim linktype conm* permco permno datadate datacqtr fq fyr fyrc fyearq fqtr ibq niq street oper bookeqLTM div atq capxy dpq dpactq 

egen id=group(permco permno)

* prepare data for tsset;  get rid of duplicate obs 
duplicates tag id fq, gen(dup)
tabulate dup

* solve problems caused by changes in FY
drop if dup>0 & fyrc!=fyr
drop dup

* in 43 cases firms report data for a fq on different datadates.  Keep the first report.  This seems to happen because of mergers
duplicates tag id fq, gen(dup)
tabulate dup

sort id fq datadate
by id fq: gen obs=_n
drop if dup==1 & obs>1
count if dup>0 & index(linkprim,"C")
drop dup obs

* generate "annual" observations from quarterly "flow" variables
generate  rd=xrdq
replace   rd=0 				if rd ==.

generate sga=0.3*(xsgaq-rd)	
replace  sga=0 				if sga==.

tsset id fq
foreach var of varlist ibq niq street oper div saleq capxy dpq xrdq xsgaq  {
	gen `var'LTM=`var'+L1.`var'+L2.`var'+L3.`var'  
}
gen ibps=ibqLTM/(adjex*cshprq)
* ******************************** 
* will need to tsset using datadate; firms that change FY cause problems
sort id datadate fq
duplicates tag id datadate, gen(dup)
tabulate dup
drop if dup>0 & fyrc!=fyr
* ******************************** 
keep conm* permco permno datadate fq *LTM ibps atq shout_compust dpactq ppegtq ppentq intanq xrdq xsgaq 
* ********************************
* put the data into a monthly frequency
egen id=group(permco permno)

gen datem=mofd(datadate)
format datem %tm

tsset id datem
tsfill

foreach var of varlist permco permno  {
	replace `var'=L1.`var' if missing(`var')==1
}
foreach var of varlist con* {
	replace `var'=`var'[_n-1] if missing(`var')==1
}
foreach var of varlist *LTM shout_compust atq ppegtq ppentq dpactq intanq xrdq xsgaq ibps { 
	by id: ipolate `var' datem, gen(tmp)
	replace `var'=tmp
	drop tmp
} 
* ********************************
sort permco permno datem
keep conm* permco permno datem *LTM shout_compust dpactq ppentq ppegtq atq ibps
/* ************************************************ */
save $Work/qtly, replace
/* ************************************************ */
use $Work/qtly, clear
